/* 
    @Author: Fromsko
    @GitHub: https://github.com/fromsko
    @DataTime: 2024/9/13 15:20
    @Finished: 2024/9/13 19:24
    @Description: Oracle 高级教程 - 练习
 */

-- 1、查询员工表中薪水大于5600的员工姓名和薪水
SELECT
    ENAME,
    SALARY
FROM
    EMP_WHC
WHERE
    SALARY > 5600;

-- 2、查询员工表中员工号为1008的员工姓名和部门号
SELECT
    ENAME,
    DEPTNO
FROM
    EMP_WHC
WHERE
    EMPNO = 1008;

-- 3、查询员工表中薪水不在6000到8000的员工的姓名和薪水
SELECT
    ENAME,
    SALARY
FROM
    EMP_WHC
WHERE
    SALARY NOT BETWEEN 6000 AND 8000
    AND SALARY IS NOT NULL;

-- 4、查询员工表中20和30号部门的员工姓名和部门号
SELECT
    ENAME,
    DEPTNO
FROM
    EMP_WHC
WHERE
    DEPTNO=20
    OR DEPTNO = 30;

-- 5、查询员工表中没有领导的员工姓名及职位，按职位排序
SELECT
    ENAME,
    JOB
FROM
    EMP_WHC
WHERE
    MGR IS NULL
ORDER BY
    JOB;

-- 6、查询员工表中有奖金的员工姓名、薪水、奖金，按薪水降序排列
SELECT
    ENAME,
    SALARY,
    BONUS
FROM
    EMP_WHC
WHERE
    BONUS IS NOT NULL
ORDER BY
    SALARY DESC;

-- 7、查询员工表中职位的第二个字符是'a'的员工姓名
SELECT
    *
FROM
    EMP_WHC
WHERE
    JOB LIKE '_a%';

/* 
SELECT
    ENAME,
    POSITION
*/
-- 8、查询部门表中部门名字和所在城市
SELECT
    DNAME,
    LOCATION AS POSITION
FROM
    DEPT_WHC;

-- 9、查询员工表中不重复的职位
SELECT
    JOB
FROM
    (
        SELECT
            JOB,
            COUNT(JOB) JOB_NUM
        FROM
            EMP_WHC
        WHERE
            JOB IS NOT NULL
        GROUP BY
            JOB
    )
WHERE
    JOB_NUM = 1;

-- 10、查询员工表中员工工号、姓名、薪水， 以及薪水提高20%后的结果
SELECT
    EMPNO,
    ENAME,
    SALARY,
    NVL(SALARY, 0) * 1.2 DEVOP_SALARY
FROM
    EMP_WHC;

-- 11、查询员工的姓名和薪水，条件限定为薪水 必须大于7200， 并对查询结果按入职时间进行排序，早入职排在前面， 晚入职排在后面
SELECT
    ENAME,
    SALARY
FROM
    EMP_WHC
WHERE
    NVL(SALARY, 0) > 7200
ORDER BY
    HIREDATE DESC;

-- 12、查询除了'研发部'部门还有哪些部门
SELECT
    DNAME
FROM
    DEPT_WHC
WHERE
    DNAME != '研发部';

-- =========================================================================================
--- 现有数据表Customer,其结构如下：
/* 
    cust_id int primary key,
    cname varchar(25) not null,
    birthday date,
    account number(5)
*/
CREATE TABLE CUSTOMER (
    CUST_ID INT PRIMARY KEY,
    CNAME VARCHAR(25) NOT NULL,
    BIRTHDAY DATE,
    ACCOUNT NUMBER(5)
);

-- 插入数据1-5
INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    1,
    'John',
    TO_DATE('1990-01-15', 'YYYY-MM-DD'),
    1000
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    2,
    'Amy',
    TO_DATE('1985-11-20', 'YYYY-MM-DD'),
    1500
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    3,
    'Tom',
    NULL,
    NULL
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    4,
    'Emily',
    TO_DATE('1992-07-05', 'YYYY-MM-DD'),
    2000
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    5,
    'David',
    TO_DATE('1988-03-10', 'YYYY-MM-DD'),
    800
);

-- 插入数据6-10
INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    6,
    'Linda',
    TO_DATE('1987-12-25', 'YYYY-MM-DD'),
    1200
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    7,
    'Michael',
    TO_DATE('1995-05-18', 'YYYY-MM-DD'),
    0
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    8,
    'Sarah',
    TO_DATE('1991-09-30', 'YYYY-MM-DD'),
    500
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    9,
    'Jason',
    TO_DATE('1993-04-08', 'YYYY-MM-DD'),
    1800
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    10,
    'Michelle',
    TO_DATE('1994-08-12', 'YYYY-MM-DD'),
    300
);

-- 插入数据11-15
INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    11,
    'Daniel',
    TO_DATE('1989-06-27', 'YYYY-MM-DD'),
    2500
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    12,
    'Sophia',
    TO_DATE('1995-02-14', 'YYYY-MM-DD'),
    700
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    13,
    'Andrew',
    TO_DATE('1992-10-01', 'YYYY-MM-DD'),
    0
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    14,
    'Jessica',
    TO_DATE('1990-11-11', 'YYYY-MM-DD'),
    800
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    15,
    'Ryan',
    TO_DATE('1987-09-05', 'YYYY-MM-DD'),
    1500
);

-- 插入数据16-20
INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    16,
    'Emma',
    TO_DATE('1993-06-01', 'YYYY-MM-DD'),
    900
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    17,
    'Kevin',
    TO_DATE('1988-07-31', 'YYYY-MM-DD'),
    1800
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    18,
    'Olivia',
    TO_DATE('1991-12-10', 'YYYY-MM-DD'),
    200
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    19,
    'William',
    TO_DATE('1994-03-22', 'YYYY-MM-DD'),
    1000
);

INSERT INTO CUSTOMER (
    CUST_ID,
    CNAME,
    BIRTHDAY,
    ACCOUNT
) VALUES (
    20,
    'Hannah',
    TO_DATE('1989-04-17', 'YYYY-MM-DD'),
    500
);

-- 1. 构造SQL语句, 列出Customer数据表中每个客户的信息。
-- 如果客户生日未提供，则该列值显示'not available'。
-- 如果没有余额信息，则显示'no account'
SELECT
    CUST_ID,
    CNAME,
    NVL(TO_CHAR(C.BIRTHDAY, 'YYYY-MM-DD'), 'NOT AVAILABLE') BIRTHDAY,
    NVL(TO_CHAR(ACCOUNT), 'no account')                     ACCOUNT
FROM
    CUSTOMER C;

-- 2. 构造SQL语句，列出生日在1987年客户的全部信息
SELECT
    CUST_ID,
    CNAME,
    TO_CHAR(BIRTHDAY, 'YYYY-MM-DD') AS BIRTHDAY,
    ACCOUNT
FROM
    CUSTOMER
WHERE
    EXTRACT(YEAR FROM BIRTHDAY) = 1987;

-- 3. 构造SQL语句，列出客户账户的余额总数
SELECT
    SUM(ACCOUNT)
FROM
    CUSTOMER
WHERE
    ACCOUNT IS NOT NULL;

-- 3-1. 按照'2009-04-11 20:35:10'格式显示系统时间
SELECT
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CURRENT_TIME
FROM
    DUAL;

-- 3-2. 构造SQL语句查询员工表中员工编号、姓名，
-- 以及月收入(薪水+奖金)，注意有的员工
-- 可能没有奖金或薪水
SELECT
    EMPNO,
    ENAME,
    NVL(SALARY, 0) + NVL(BONUS, 0) AS MONTHLY_INCOME
FROM
    EMP_WHC;

-- 4. 列出每个员工的姓名、薪水、
-- 涨薪后工资(涨幅为8%)，元为单位进行四舍五入
SELECT
    ENAME,
    SALARY,
    ROUND(SALARY * 1.08, 0) AS INCREASED_SALARY
FROM
    EMP_WHC;

-- 5. 查询各个职位的员工人数
SELECT
    JOB,
    COUNT(JOB)
FROM
    EMP_WHC
GROUP BY
    JOB;

-- 6. 查询员工最高薪水和最低薪水的差距，列名为DIFFERENCE
SELECT
    (MAX_SAL - MIN_SAL) AS DIFFERENCE
FROM
    (
        SELECT
            MIN(SALARY) MIN_SAL,
            MAX(SALARY) MAX_SAL
        FROM
            EMP_WHC
        WHERE
            SALARY IS NOT NULL
    );

-- 7. 哪一子句可实现SELECT语句查询员工平均薪水小于5000的部门信息？(A)
/* 
    A.GROUP BY dept_id WHERE AVG(salary)<5000
    B.GROUP BY AVG(salary) HAVING AVG(salary)<5000
    C.GROUP BY dept_id HAVING AVG(salary)<5000
    D.GROUP BY AVG(salary)<5000
*/

-- 8. Student表的表结构
/*
(
    sid_id int
    start_date date
    end_date date
)
*/

-- 在 start_date 列上使用哪些函数是正确的？(C)
/*
    A.sum(start_date)
    B.count(start_date)
    C.avg(start_date,end_date)
    D.min(start_date)
*/


/*
    11. 当执行以下查询语句
        SELECT empno,ename
        from emp_xu
        where empno=7782 or empno=7876;

    在WHERE语句中，可以实现与OR相同功能的操作符是? (A)
        A.IN
        B.BETWEEN...AND..
        C.LIKE
        D.AND
*/


-- ==========================================================================================
-- 1、显示所有员工的姓名、部门号和部门名称
SELECT
    E.ENAME,
    E.DEPTNO,
    D.DNAME
FROM
    EMP_WHC  E,
    DEPT_WHC D;

-- 2、查询在'南京'工作的员工的姓名、职位、部门号、部门名称
SELECT
    E.ENAME,
    E.JOB,
    E.DEPTNO,
    D.DNAME
FROM
    EMP_WHC  E,
    DEPT_WHC D
WHERE
    D.LOCATION = '南京';

-- 3、查询所有员工的员工号、姓名以及他的领导的员工号和姓名
SELECT
    M.EMPNO,
    M.ENAME,
    E.EMPNO MANAGER,
    E.ENAME MANAGER_NAME
FROM
    EMP_WHC E
    INNER JOIN EMP_WHC M
    ON E.EMPNO = M.MGR;

-- 4、查询各部门员工姓名和他们所在位置
-- Select rows from a Table
SELECT
    ENAME,
    LOCATION
FROM
    EMP_WHC,
    DEPT_WHC;

-- 5、查询出'张无忌'的领导是谁
SELECT
    E.ENAME
FROM
    EMP_WHC E
WHERE
    E.EMPNO = (
        SELECT
            MGR
        FROM
            EMP_WHC
        WHERE
            ENAME = '张无忌'
    );

-- 6、'张三丰'领导谁
SELECT
    ENAME
FROM
    EMP_WHC
WHERE
    MGR IN ( -- 张三丰 ID
        SELECT
            EMPNO
        FROM
            EMP_WHC
        WHERE
            ENAME = '张三丰'
    );

-- 7、查询和'张三丰'相同部门的员工姓名和雇佣日期
SELECT
    ENAME,
    HIREDATE
FROM
    EMP_WHC
WHERE
    DEPTNO IN ( -- 张三丰的部门
        SELECT
            DEPTNO
        FROM
            EMP_WHC
        WHERE
            ENAME = '张三丰'
    )
    AND ENAME <> '张三丰';

-- 8、查询薪水比公司平均薪水高的所有员工的员工号、姓名和薪水
WITH T AS (
    SELECT
        DEPTNO,
        AVG(NVL(SALARY, 0)) SALARY
    FROM
        EMP_WHC
    GROUP BY
        DEPTNO
)
SELECT
    DISTINCT E.EMPNO,
    E.ENAME,
    E.SALARY
FROM
    EMP_WHC E,
    T
WHERE
    E.SALARY > T.SALARY;

-- 9、查询领导是'张三丰'的员工姓名和薪水
SELECT
    ENAME,
    SALARY
FROM
    EMP_WHC
WHERE
    MGR = (
        SELECT
            EMPNO
        FROM
            EMP_WHC
        WHERE
            ENAME = '张三丰'
    );

-- 10、显示'研发部'有哪些职位
SELECT
    JOB
FROM
    EMP_WHC
WHERE
    DEPTNO = (
        SELECT
            DEPTNO
        FROM
            DEPT_WHC
        WHERE
            DNAME = '研发部'
    );

-- 11、各个部门中薪水大于5000的员工人数
SELECT
    DEPTNO,
    COUNT(SALARY)
FROM
    EMP_WHC
WHERE
    SALARY > 5000
GROUP BY
    DEPTNO;

-- 13、所在部门平均薪水高于5000的员工名字 [题目有二义性]
SELECT
    DEPTNO
FROM
    (
        SELECT
            DEPTNO,
            AVG(SALARY) SALARY
        FROM
            EMP_WHC
        GROUP BY
            DEPTNO
    )
WHERE
    SALARY > 5000;

-- 14、列出各个部门中薪水最高的员工的姓名、部门号和薪水
SELECT
    ENAME,
    DEPTNO,
    SALARY
FROM
    EMP_WHC
WHERE
    (SALARY, DEPTNO) IN (
        SELECT
            MAX(SALARY) SALARY,
            DEPTNO
        FROM
            EMP_WHC
        GROUP BY
            DEPTNO
    );

-- 15、下列哪个组合查询返回两个查询所选择的所有的行 (B)
/* 
    A.Union
    B.Union all
    C.Union only
    D.connect by
*/

-- 16. 从员工表的'姓名'字段中找出名字包含'玛丽'的人，下面哪条SELECT语句正确 (D)

/* 
    A.Select * from 员工 where 姓名='_玛丽_';
    B.Select * from 员工 where 姓名='%玛丽_';
    C.Select * from 员工 where 姓名 LIKE '_玛丽%';
    D.Select * from 员工 where 姓名 LIKE '%玛丽%';
*/

-- 17. 检查下列数据表中的数据

/*
    LAST_NAME   DEPARTMENT_ID    SALARY
    Getz              10         3000
    Davis             20         1500
    King              20         2200
    Davis             30         5500

    下面子查询正确的是? (A)
    A.select * from emp where salary>
        (
            select min(salary)
            from emp group by department_id
        );
    B.select * from emp where salary=(
            select avg(salary)
            from emp group by department_id
        );
    C.select department_id from emp 
        where salary>all(select avg(salary)
            from emp group by department_id
        );
    D.select last_name from emp 
        where salary>any(select max(salary)
            from emp group by department_id
        );
*/